package com.gljx.web.controller;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.gljx.util.*;
import org.aspectj.weaver.ast.Or;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import com.gljx.web.Entity.Car;
import com.gljx.web.Entity.Department;
import com.gljx.web.Entity.PageBean;
import com.gljx.web.Entity.Person;
import com.gljx.web.Entity.PersonEntrys;
import com.gljx.web.Entity.Positiongrade;
import com.gljx.web.Entity.User;
import com.gljx.web.Entity.personData;
import com.gljx.web.service.CarService;
import com.gljx.web.service.CustomLogService;
import com.gljx.web.service.DepartmentService;
import com.gljx.web.service.PersonEntrysService;
import com.gljx.web.service.PersonService;
import com.gljx.web.service.PositiongradeService;
import com.gljx.web.service.UserService;

@Controller
@RequestMapping("/")
public class PersonController {

	//自动注入
	@Autowired
	private PersonService personService;
	
	//自动注入
	@Autowired
	private CarService carService;
	
	//自动注入
	@Autowired
	private DepartmentService departmentService;
	
	//自动注入
	@Autowired
	private CustomLogService customLogService;
	
	//自动注入
	@Autowired
	private PositiongradeService positiongradeService;

	//自动注入
	@Autowired
	private PersonEntrysService personEntrysService;
	
	@Autowired
	private UserService userService;
	/**
	 * 查询人员
	 * @createTime 2017-11-6 5:20 pm
	 * @author zyq.
	 * @return
	 */
	@ResponseBody
	@RequestMapping(value="/selectPerson")
	public List<Person> selectPerson() {
		String psql="select T.FID, T.FNUMBER, T.FSIMPLENAME, T.FCREATORID,"
				   + "to_char(t.CFBIRTHDAY,'YYYY-MM-DD ') cfbirthdays,"
				   +"to_char(t.cfstartdate,'YYYY-MM-DD ') cfstartdate,"
				   + "to_char(t.cfenddate,'YYYY-MM-DD ') cfenddate,"
				   +"to_char(t.cfbegindate,'YYYY-MM-DD ') cfbegindate,"
				   + "to_char(t.CFHTDQSJ,'YYYY-MM-DD ') cfhtdqsj,"
					+ " T.FLASTUPDATEUSERID,T.FLASTUPDATETIME,T.FCONTROLUNITID, T.CFFICOMPANYID, T.CFIDCARDNO, T.CFCELL, T.CFADDRESS, T.CFPERSONDEPID, T.CFWEDID,"
					+ " T.CFHEIGHT, T.CFMAXDEGREEID, T.CFFOLKID, T.CFPOLITICALFACEID, T.CFHEALTHID, "
					+ "T.CFACCOUNTSNO, T.CFGRADUATESCHOOL, T.CFSPECIALTY,  T.CFCARID, T.CFCCLZSJ, "
					+ "T.CFJLZYXQS, T.CFJLZYXQZ, T.CFZJFW, T.CFZJZH, T.CFPERSONTYPE, T.CFSEX, T.CFJOBGRADEID, T.CFSYSPERSONID, "
					+ "T.CFISVIP, T.CFSUBJECT, T.CFXCTZ, T.CFGWYD, T.CFQJJL, T.CFWJJLJL, T.CFSHJLJL,"
					+ "T.FNAME_L2,   T.FDESCRIPTION_L2,  T.CFZSTC, T.CFPXTC , A.FNAME_L2 AS CFPERSONDEPNAME , C.FNAME_L2 AS CFCARNAME from ct_ds_person t left join CT_DS_ADMIN A ON A.FID=T.CFPERSONDEPID "
					+ "left join CT_DS_CAR C ON C.FID=T.CFCARID ";
		
		List<Person> persons=personService.selectPerson(psql);
		return persons;
	}
	
	/**
	 * 根据姓名查询人员
	 * @createTime 2017-11-7 10:20 am
	 * @author zyq.
	 * @param name
	 * @return
	 */
	@ResponseBody
	@RequestMapping(value="/selPersonByName")
	public List<Person> selPersonByName(String name) {
		List<Person> persons=personService.selectPersonByName(name);
		return persons;	
	}
	
	
	/**
	 * 根据id查询人员
	 * @createTime 2017-11-7 10:42 am
	 * @author zyq.
	 * @param id
	 * @return
	 */
	@ResponseBody
	@RequestMapping(value="/selPersonById")
	public List<Person> selPersonById(String id){
		List<Person> persons=new ArrayList<>();
		String [] ids=id.split(",");
		for(int i=0;i<ids.length;i++){                                           //获取多个ID
			Person person=personService.selTrainTypeById(ids[i]);
			persons.add(person);
		}	
		return persons;
	}

	@ResponseBody
	@RequestMapping(value="/selPersonByCarId")
	public List<Person> selPersonByCarId(String carId){

		return personService.selectPersonForByCarId(carId);
	}


	@RequestMapping("/queryPerson/look.action")
	public String queryPerson(HttpServletRequest request, String flag) {
		PageBean<Person>pb = new PageBean<>();
		//获取操作人信息
	    User user = (User)request.getSession().getAttribute("user");
		try {
			//获取分页
			pb = getPageBean(request,user.getFcompanyid());
			//获取当前分页的值
			List<Person> persons = pb.getBeanList();
			//把获取到的数据传回页面
			request.setAttribute("persons", persons);
			request.setAttribute("pb", pb);
			request.setAttribute("flag", flag);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return "/backHome/personData/person/personList";
	}
	
	/**
	 * 根据条件查询
	 * @param request
	 * @param fnameL2 名称
	 * @param cfcell 手机号
	 * @param cfidcardno 身份证明
	 * @param cfaccountsno 社保编号
	 * @return
	 */
	@RequestMapping("/queryPersonByInfo")
	public String queryPersonByInfo(HttpServletRequest request,String fnameL2,String cfcell,String cfidcardno,String cfaccountsno){
		PageBean<Person>pb = new PageBean<>();
		//获取操作人信息
	    User user = (User)request.getSession().getAttribute("user");
	    if("".equals(fnameL2) && "".equals(cfcell) && "".equals(cfidcardno) && "".equals(cfaccountsno)){
			queryPerson(request, null);//如果都未空就调用查询的方法
		}else{
            //去除空格
            fnameL2 = fnameL2.trim();
            cfcell = cfcell.trim();
            cfidcardno = cfidcardno.trim();
            cfaccountsno = cfaccountsno.trim();
            //拼接SQL语句
	    	StringBuffer psql = new StringBuffer();
	    	psql.append("select T.FID, T.FNUMBER,to_char(t.CFBIRTHDAY,'YYYY-MM-DD ') cfbirthdays,to_char(t.cfstartdate,'YYYY-MM-DD ') cfstartdate,");
			psql.append("to_char(t.cfenddate,'YYYY-MM-DD ') cfenddate,to_char(t.cfbegindate,'YYYY-MM-DD ') cfbegindate,to_char(t.CFHTDQSJ,'YYYY-MM-DD ') cfhtdqsj,");
			psql.append("T.CFIDCARDNO, T.CFCELL, T.CFADDRESS,T.CFPERSONTYPE, T.CFSEX,T.CFXCTZ, T.CFGWYD, T.CFQJJL, T.CFWJJLJL,p.fname_l2 as positiongradeName,t.cfaccountsno, ");
			psql.append("T.CFSHJLJL,T.FNAME_L2,T.CFZSTC,T.CFPXTC,A.FNAME_L2 AS CFPERSONDEPNAME,C.fnumber AS CFCARNAME from ct_ds_person t left join ");
			psql.append("CT_DS_ADMIN A ON A.FID=T.CFPERSONDEPID left join CT_DS_CAR C ON C.FID=T.CFCARID left join CT_DS_POSITIONGRADE p on t.cfjobgradeid=p.fid ");
			psql.append(" where t.FCONTROLUNITID= '"+user.getFcompanyid()+"'");
			//判断条件
			if(fnameL2!=""){
				psql.append(" and t.fname_l2 like '%"+fnameL2+"%' ");
			}
			if(cfcell!=""){
                psql.append(" and t.cfcell like '%" + cfcell + "%' ");
            }
			if(cfidcardno!=""){
                psql.append(" and t.cfidcardno like '%" + cfidcardno + "%' ");
            }
			if(cfaccountsno!=""){
				psql.append(" and t.cfaccountsno = '"+cfaccountsno+"' ");
			}
			psql.append(" order by t.FCREATETIME desc");
			//获取根据条件查询到的数据数据
			List<Person> persons=personService.selectPerson(psql.toString());
			//总记录数
			pb.setTr(persons.size());
			//每页记录数
            pb.setPs(1000);
            //把数据传到页面上
			request.setAttribute("persons", persons);
			request.setAttribute("pb", pb);
			request.setAttribute("fnameL2", fnameL2);
			request.setAttribute("cfcell", cfcell);
			request.setAttribute("cfidcardno", cfidcardno);
			request.setAttribute("cfaccountsno", cfaccountsno);
	    }
		return "/backHome/personData/person/personList";
	}
	
	/**
	 * 跳转到添加页面
	 * @param request
	 * @return
	 */
	@RequestMapping("/insertPerson")
	public String insertPerson(HttpServletRequest request,String type){
		//获取操作人信息
	    User user = (User)request.getSession().getAttribute("user");
	    //获取车辆
		List<Car> cars = carService.queryCar(user.getFcompanyid());
		//获取组织
	    Department department = departmentService.queryAdminByFid(user.getFcompanyid());
		//获取最大值员工编号
        /*String fnumber = customLogService.selectFunmber("ct_ds_person",user.getFcompanyid());*/
		//获取编码
        String number = GetFnumber.fnumbers("ZY");
		//获取部门
	    List<Department> departments = departmentService.queryAdminByController(user.getFcompanyid());
	    //查询健康状况
	    List<personData> healthys = customLogService.selectHealthyAll();
	    //查询婚姻状况 
	    List<personData> marriages = customLogService.selectMarriageAll();
	    //查询政治面貌
	    List<personData> politicals = customLogService.selectPoliticalAll();
	    //查询民族
	    List<personData> familyNames = customLogService.selectFamilyNameAll();
	    //查询最高学历
	    List<personData> edcations = customLogService.selectEdcationAll();
		//把数据传到页面
	    request.setAttribute("edcations", edcations);
	    request.setAttribute("familyNames", familyNames);
	    request.setAttribute("politicals", politicals);
	    request.setAttribute("marriages", marriages);
	    request.setAttribute("healthys", healthys);
		request.setAttribute("funmber", number);
		request.setAttribute("departments", departments);
		request.setAttribute("department", department);
		request.setAttribute("cars", cars);
		request.setAttribute("type", type);
		return "/backHome/personData/person/addPerson";
	}
	
	/**
	 * 查询职级职等
	 * @return
	 */
	@RequestMapping("/selectZjzd")
	@ResponseBody
	public List<Positiongrade> selectZjzd(HttpServletRequest request){
		//获取操作人信息
	    User user = (User)request.getSession().getAttribute("user");
	    //查询数据
		List<Positiongrade> positiongradeList = positiongradeService.queryZjzdByCompanyId(user.getFcompanyid());
		return positiongradeList;
	}
	
	/**
	 * 根据名称查询职级职等
	 * @param request
	 * @param name 要查询的名称
	 * @return
	 */
	@RequestMapping("/selectZjzdByName")
	@ResponseBody
	public List<Positiongrade> selectZjzdByName(HttpServletRequest request,String name){
		//获取操作人信息
	    User user = (User)request.getSession().getAttribute("user");
	    //查询数据
	    List<Positiongrade> positiongradeList = positiongradeService.queryZjzdByCompanyIdAndFnameL2(user.getFcompanyid(), name);
	    return positiongradeList;
	}
	
	/**
	 * 根据fid查询职级职等
	 * @param fid
	 * @return
	 */
	@RequestMapping("/selectZjzdByFid")
	@ResponseBody
	public Positiongrade selectZjzdByFid(String fid){
		Positiongrade positiongrade = positiongradeService.queryPositiongradeByFid(fid);
		return positiongrade;
	}
	/**
	 * 根据名称查询车辆
	 * @param name
	 * @return
	 */
	@RequestMapping("/selCarByName")
	@ResponseBody
	public List<Car> selCarByName(String name, HttpServletRequest request) {
		//获取操作人信息
		User user = (User) request.getSession().getAttribute("user");
		List<Car> cars = new ArrayList<>();
		if(name == null || "".equals(name)){
			cars = carService.queryCar(user.getFcompanyid());
		}else{
			cars = carService.queryCarByName(name, user.getFcompanyid());
		}
		return cars;
	}
	
	/**
	 * 根据fid查询
	 * @param fid 要查询的fid
	 * @return
	 */
	@RequestMapping("/selCarByFid")
	@ResponseBody
	public Car selCarByFid(String fid){
		Car car = carService.queryCarByFid(fid);
		return car;
	}
	
	/**
	 * 添加职员
	 * @param request
	 * @param person 属性自动封装
	 * @return
	 */
	@RequestMapping("/addPerson")
	public String addPerson(HttpServletRequest request,Person person){
		//获取操作人信息
		User user = (User)request.getSession().getAttribute("user");
		//利用UUID生成fid
		String fid = GetFid.UUIDIntoFid();
		person.setFid(fid);
		//控制单元id
		person.setFcontrolunitid(user.getFcompanyid());
		//创建人id
		person.setFcreatorid(user.getFid());
		//公司id
		person.setCfficompanyid(user.getFcompanyid());
		//获取当前时间
		String createTime = TimeFormat.timeFormat(new Date());
		person.setFcreatetime(createTime);
		//把属性添加进去
		personService.addPerson(person);
		//获取子类信息
		String[] fseqs = request.getParameterValues("fseq");
		String[] cfkemus = request.getParameterValues("cfkemu");
		String[] cfcartypes = request.getParameterValues("cfcartype");
		//添加子类信息
		if(fseqs == null || fseqs.length == 0){
			
		}else{
			for (int i = 0; i < fseqs.length; i++) {
				PersonEntrys personEntrys = new PersonEntrys();
				personEntrys.setFid(GetFid.UUIDIntoFid());
				personEntrys.setCfcartype(cfcartypes[i]);
				if(cfkemus[i] == null || "".equals(cfkemus[i])){
					
				}else{
					personEntrys.setCfkemu(Long.parseLong(cfkemus[i]));
				}
				personEntrys.setFparentid(fid);
				personEntrys.setFseq((long)i+1);
				personEntrysService.addPersonEntrys(personEntrys);
			}
		}
		//添加日志
		customLogService.addLog("职员基础资料", createTime+"对职员"+person.getFnameL2()+"进行添加操作", null, user.getFid());
		//重定向到添加页面
		return "redirect:/DrivingSys/insertPerson?type=3";
	}
	
	/**
	 * 根据fid删除
	 * @param request
	 * @param deleteId 多个fid
	 * @param fid 单个fid
	 * @return
	 */
	@RequestMapping("/deletePersonByFid")
	public String deletePersonByFid(HttpServletRequest request,String deleteId[],String fid){
		//获取操作人信息
		User user = (User)request.getSession().getAttribute("user");
		if(fid == null || "".equals(fid)){
			for (int i = 0; i < deleteId.length; i++) {
				//删除子类信息
				personEntrysService.deleteByFparentId(deleteId[i]);
				//删除父类信息
				personService.deletePersonByFid(deleteId[i]);
			}
		}else{
			//删除子类信息
			personEntrysService.deleteByFparentId(fid);
			//删除父类信息
			personService.deletePersonByFid(fid);
		}
		//添加日志
		customLogService.addLog("职员基础资料", TimeFormat.timeFormat(new Date())+"对职员进行删除操作", null, user.getFid());
		//调用查询的方法
		queryPerson(request, null);
		return "/backHome/personData/person/personList";
	}
	
	/**
	 * 根据fid查询
	 * @param request
	 * @param fid 要查询的fid
	 * @return
	 */
	@RequestMapping("/selectPersonByFid")
	public String selectPersonByFid(HttpServletRequest request,String fid){
		//获取操作人信息
	    User user = (User)request.getSession().getAttribute("user");
	    //获取车辆
		List<Car> cars = carService.queryCar(user.getFcompanyid());
		//获取组织
	    Department department = departmentService.queryAdminByFid(user.getFcompanyid());
		//获取部门
	    List<Department> departments = departmentService.queryAdminByController(user.getFcompanyid());
	    //查询数据
	    Person person = personService.queryPersonByFid(fid);
	    //查询子类数据
	    List<PersonEntrys> personEntrys = personEntrysService.queryPersonEntrysByFparentid(fid);
	    //查询健康状况
	    List<personData> healthys = customLogService.selectHealthyAll();
	    //查询婚姻状况 
	    List<personData> marriages = customLogService.selectMarriageAll();
	    //查询政治面貌
	    List<personData> politicals = customLogService.selectPoliticalAll();
	    //查询民族
	    List<personData> familyNames = customLogService.selectFamilyNameAll();
	    //查询最高学历
	    List<personData> edcations = customLogService.selectEdcationAll();
	    //查询所有车型
	    List<String> carTypes = customLogService.queryCarTypeAll();
		//把数据传到页面
	    request.setAttribute("carTypes", carTypes);
	    request.setAttribute("edcations", edcations);
	    request.setAttribute("familyNames", familyNames);
	    request.setAttribute("politicals", politicals);
	    request.setAttribute("marriages", marriages);
	    request.setAttribute("healthys", healthys);
	    request.setAttribute("person", person);
	    request.setAttribute("personEntrys", personEntrys);
		request.setAttribute("departments", departments);
		request.setAttribute("department", department);
		request.setAttribute("cars", cars);
		return "/backHome/personData/person/updatePerson";
	}
	
	/**
	 * 修改职员
	 * @param request
	 * @param person
	 * @return
	 */
	@RequestMapping("/modifyPerson")
	public String modifyPerson(HttpServletRequest request,Person person){
		//获取操作人信息
		User user = (User)request.getSession().getAttribute("user");
		//控制单元id
		person.setFcontrolunitid(user.getFcompanyid());
		//修改人id
		person.setFlastupdateuserid((user.getFid()));
		//公司id
		person.setCfficompanyid(user.getFcompanyid());
		//获取当前时间
		String updateTime = TimeFormat.timeFormat(new Date());
		person.setFlastupdatetime(updateTime);
		//把属性添加进去
		personService.modifyPerson(person);
		//获取子类信息
		String[] fseqs = request.getParameterValues("fseq");
		String[] cfkemus = request.getParameterValues("cfkemu");
		String[] cfcartypes = request.getParameterValues("cfcartype");
		//删除之前子类信息
		personEntrysService.deleteByFparentId(person.getFid());
		//添加子类信息
		if(fseqs == null || fseqs.length == 0){
					
		}else{
			for (int i = 0; i < fseqs.length; i++) {
				PersonEntrys personEntrys = new PersonEntrys();
				personEntrys.setFid(GetFid.UUIDIntoFid());
				personEntrys.setCfcartype(cfcartypes[i]);
				if(cfkemus[i] == null || "".equals(cfkemus[i])){
							
				}else{
					personEntrys.setCfkemu(Long.parseLong(cfkemus[i]));
				}
				personEntrys.setFparentid(person.getFid());
				personEntrys.setFseq((long)i+1);
				personEntrysService.addPersonEntrys(personEntrys);
			}
		}
		//添加日志
		customLogService.addLog("职员基础资料", TimeFormat.timeFormat(new Date())+"对职员"+person.getFnameL2()+"进行修改操作", null, user.getFid());
		return commonController.skipUrl("/queryPerson/look.action");
	}
	
	/**
	 * 查询系统用户
	 * @param request
	 * @return
	 */
	@RequestMapping("/selectUser")
	@ResponseBody
	public List<User> selectUser(HttpServletRequest request){
		//获取操作人信息
		User user = (User)request.getSession().getAttribute("user");
		//获取系统用户
		List<User> users = userService.queryUserByCompanyId(user.getFcompanyid());
		return users;
	}
	/**
	 * 根据名称查询
	 * @param name
	 * @param request
	 * @return
	 */
	@RequestMapping("/selectUserByName")
	@ResponseBody
	public List<User> selectUserByName(String name,HttpServletRequest request){
		//获取操作人信息
		User user = (User)request.getSession().getAttribute("user");
		//查询系统用户
		List<User> users = userService.queryUserByCompanyIdAndName(user.getFcompanyid(), name);
		return users;
	}
	
	/**
	 * 根据fid查询
	 * @param fid
	 * @return
	 */
	@RequestMapping("/selUserByFid")
	@ResponseBody
	public User selUserByFid(String fid){
		User user = userService.queryUserByFid(fid);
		return user;
	}
	
	//查询所有车型
	@RequestMapping("/selectCarTypeAll")
	@ResponseBody
	public List<String> selectCarTypeAll(){
		List<String> carTypes = customLogService.queryCarTypeAll();
		return carTypes;
	}
	
	@SuppressWarnings("unchecked")
	/**
	 * 返回PageBean实体类  包括记录总数，页码，每页记录集等  dmz 20160921
	 * @return 返回PageBean实体类 
	 * @throws Exception
	 */
	public PageBean<Person> getPageBean(HttpServletRequest request,String companyId) throws Exception
	{

		int offset;//从第几条数据开始加载
		if(request.getParameter("pager.offset")==null){  
			offset=0;  
		}else{  
			offset=Integer.parseInt(request.getParameter("pager.offset"));  
		}	
		//拼接SQL语句
		StringBuffer psql = new  StringBuffer();
		psql.append("select T.FID, T.FNUMBER,to_char(t.CFBIRTHDAY,'YYYY-MM-DD ') cfbirthdays,to_char(t.cfstartdate,'YYYY-MM-DD ') cfstartdate,");
		psql.append("to_char(t.cfenddate,'YYYY-MM-DD ') cfenddate,to_char(t.cfbegindate,'YYYY-MM-DD ') cfbegindate,to_char(t.CFHTDQSJ,'YYYY-MM-DD ') cfhtdqsj,");
		psql.append("T.CFIDCARDNO, T.CFCELL, T.CFADDRESS,T.CFPERSONTYPE, T.CFSEX,T.CFXCTZ, T.CFGWYD, T.CFQJJL, T.CFWJJLJL,p.fname_l2 as positiongradeName,t.cfaccountsno, ");
		psql.append("T.CFSHJLJL,T.FNAME_L2,T.CFZSTC,T.CFPXTC,A.FNAME_L2 AS CFPERSONDEPNAME,C.fnumber AS CFCARNAME from ct_ds_person t left join ");
		psql.append("CT_DS_ADMIN A ON A.FID=T.CFPERSONDEPID left join CT_DS_CAR C ON C.FID=T.CFCARID left join CT_DS_POSITIONGRADE p on t.cfjobgradeid=p.fid ");
		psql.append(" where t.FCONTROLUNITID= '"+companyId+"' order by t.fcreatetime desc");
		int psize=15; //每页显示数
		int pindex=offset/psize;//+1  页码从0开始
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap= personService.selectPage(pindex, psql.toString(), psize);
		PageBean<Person> pb=new PageBean<>();
		pb.setPc(pindex); //页码
		Map<String, Object> paramMapCount=getPageBeanCount(psql.toString()); //取记录总数
		pb.setTr((int)paramMapCount.get("Pcount")); //记录总数
		pb.setPs(psize);
		pb.setBeanList((List<Person>)paramMap.get("v_cur")); //每页数据集
		return pb;
	}

    /**
     * @Author lcx
     * @Date 2017/11/16 0016 上午 8:47
     * 查询科目二教练
     */
    @SuppressWarnings("unchecked")
    @ResponseBody
    @RequestMapping(value = "/getPageAsJl")
	public Map getPageAsJl(HttpServletRequest request, @RequestParam(defaultValue = "", value = "name") String name, @RequestParam(defaultValue = "") String cfsjhm, @RequestParam(defaultValue = "1") String pindex) throws Exception {

		User user = (User) request.getSession().getAttribute("user");

        String psql = "SELECT " +
                "  p.fid," +
                "  p.FNUMBER," +
                "  p.FNAME_L2," +
                "  p.CFIDCARDNO," +
                "  p.CFSEX," +
                "  p.CFCELL," +
                "  p.CFISVIP," +
                "	p1.CFKEMU," +
                "  p.CFZJZH," +
                "  p.CFZJFW," +
                "to_char(p.CFJLZYXQS, 'YYYY-MM-DD')  CFJLZYXQS," +
                "to_char(p.CFJLZYXQZ, 'YYYY-MM-DD')  cfjlzyxqz," +
                "to_char(p.CFENDDATE, 'YYYY-MM-DD')  CFENDDATE" +
                "	FROM CT_DS_PERSON p LEFT JOIN ct_ds_personentrys p1 ON p.fid = p1.FPARENTID" +
				"	WHERE p.CFPERSONTYPE = 2 and p.FCONTROLUNITID = '" + user.getFcompanyid() +
				"' and p.FNAME_L2 like '%" + name + "%'";

        int psize = 50; //每页显示数
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap = personService.selectPage(Integer.valueOf(pindex), psql, psize);
        PageBean<Person> pb = new PageBean<>();
        pb.setPc(Integer.valueOf(pindex)); //页码
        Map<String, Object> paramMapCount = getPageBeanCount(psql); //取记录总数
        pb.setTr((int) paramMapCount.get("Pcount")); //记录总数
        pb.setPs(psize);
        Map stu = new HashMap();
        stu.put("sList", (List<Person>) paramMap.get("v_cur"));

        stu.put("pb", (pb.getTr() / pb.getPs()));

        return stu; //每页数据集;

    }

    /**
     * @Author lcx
     * @Date 2017/11/21 0021 上午 10:37
     * ajax查询教练列表
     */
    @RequestMapping(value = "/selectPersonList")
    @ResponseBody
	public Object selPositiongradeById(HttpServletRequest request,
									   @RequestParam(value = "carType", defaultValue = "A1") String carType,
									   @RequestParam(value = "kemu", defaultValue = "A1") String kemu) throws Exception {
        List<Person> personArrayList = new ArrayList<Person>();
		User user = (User) request.getSession().getAttribute("user");
		personArrayList = personService.queryPersonList(carType, kemu, user.getFcompanyid());
        return personArrayList;
    }

	/**
	 * 取记录总数 dmz 20160921
	 * @param psql  SQL语句
	 * @return Map
	 * @throws Exception
	 */
	public Map<String, Object> getPageBeanCount(String psql) throws Exception
	{

		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap=personService.selectPageCount(psql);
		return paramMap;		
	}


	/**
	 * 导出Excel
	 *
	 * @param response
	 * @param request
	 * @param fnameL2
	 * @param cfcell
	 * @param cfidcardno
	 * @param cfaccountsno
	 */
	@RequestMapping("/importPerson")
	public void importPerson(HttpServletResponse response, HttpServletRequest request, String fnameL2, String cfcell, String cfidcardno, String cfaccountsno) {
		//获取操作人信息
		User user = (User) request.getSession().getAttribute("user");
		//定义接收类型
		List<Object> data = new ArrayList<>();
		//查询数据
		StringBuffer psql = new StringBuffer();
		psql.append("select T.FNUMBER,to_char(t.CFBIRTHDAY,'YYYY-MM-DD ') cfbirthdays,to_char(t.cfstartdate,'YYYY-MM-DD ') cfstartdate,");
		psql.append("to_char(t.cfenddate,'YYYY-MM-DD ') cfenddate,to_char(t.cfbegindate,'YYYY-MM-DD ') cfbegindate,to_char(t.CFHTDQSJ,'YYYY-MM-DD ') cfhtdqsj,");
		psql.append("T.CFIDCARDNO, T.CFCELL, T.CFADDRESS,T.CFPERSONTYPE, T.CFSEX,T.CFXCTZ, T.CFGWYD, T.CFQJJL, T.CFWJJLJL,p.fname_l2 as positiongradeName,t.cfaccountsno, ");
		psql.append("T.CFSHJLJL,T.FNAME_L2,T.CFZSTC,T.CFPXTC,A.FNAME_L2 AS CFPERSONDEPNAME,C.fnumber AS CFCARNAME from ct_ds_person t left join ");
		psql.append("CT_DS_ADMIN A ON A.FID=T.CFPERSONDEPID left join CT_DS_CAR C ON C.FID=T.CFCARID left join CT_DS_POSITIONGRADE p on t.cfjobgradeid=p.fid ");
		psql.append(" where t.FCONTROLUNITID= '" + user.getFcompanyid() + "'");
		//判断条件
		if (fnameL2 != "") {
            psql.append(" and t.fname_l2 like '%" + fnameL2.trim() + "%' ");
        }
		if (cfcell != "") {
            psql.append(" and t.cfcell like '%" + cfcell.trim() + "%' ");
        }
		if (cfidcardno != "") {
            psql.append(" and t.cfidcardno like '%" + cfidcardno.trim() + "%' ");
        }
		if (cfaccountsno != "") {
            psql.append(" and t.cfaccountsno = '" + cfaccountsno.trim() + "' ");
        }
		psql.append(" order by t.FCREATETIME desc");
		//获取根据条件查询到的数据数据
		List<Person> persons = personService.selectPerson(psql.toString());
		for (Person person : persons) {
			data.add(person.getFnumber());
			data.add(person.getFnameL2());
			data.add(person.getCfpersondepName());
			data.add(stateToString.personType(person.getCfpersontype()));
			data.add(person.getCfidcardno());
			data.add(person.getCfcell());
			data.add(person.getCfaddress());
			data.add(person.getCfbirthdays());
			data.add(person.getCfbegindate());
			data.add(person.getCfstartdate());
			data.add(person.getCfenddate());
			data.add(person.getCfhtdqsj());
			data.add(person.getCfaccountsno());
			data.add(person.getCfcarName());
			data.add(stateToString.sex(person.getCfsex()));
			data.add(person.getPositiongradeName());
			data.add(person.getCfxctz());
			data.add(person.getCfgwyd());
			data.add(person.getCfqjjl());
			data.add(person.getCfwjjljl());
			data.add(person.getCfshjljl());
			data.add(stateToString.whether(person.getCfzstc()));
			data.add(stateToString.whether(person.getCfpxtc()));
		}

		//头部数据
		String head[] = {"员工编号", "员工姓名", "部门", "员工类型", "身份证号码", "手机号码", "地址", "出生日期", "入职日期", "转正日期", "离职日期",
				"合同到期日", "社保编号", "车辆", "性别", "职级职等", "薪酬调整", "岗位异动", "请假记录", "违纪奖励记录", "社会保险记录", "计算招生提成", "计算培训提成"};
		//长度
		Integer length[] = {6500, 3000, 3000, 3000, 5000, 3500, 8000, 4000, 4000, 4000, 4000, 4000, 3500, 3000, 1600, 3000, 3000, 3000, 5600, 5600, 5600, 2000, 2000};
		try {
			//导出Excel
			CommonExport.importExcel(response, "职员" + TimeFormat.getNowTime2() + ".xls", "职员", data, head, length);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
